const express = require('express');
const mysql = require('mysql');
const common = require('../libs/common');
const db = mysql.createPool({
    host: '129.28.42.105',
    user: 'root',
    password: '123456',
    database: 'mysql'
});
module.exports = () => {
    const route = express.Router();
    const getHomeStr = `SELECT product_id,product_name,product_price,product_img_url,product_uprice,product_num,product_detail FROM product`;
    const getCateNames = `SELECT * FROM category ORDER BY category_id ASC`;
    const getNewsNames='select * from news order by id asc';
    //get homePage datas
    route.get('/home', (req, res) => {
        getHomeDatas(getHomeStr, res);
    });
    route.get('/message', (req, res) => {

        const keywordStr = `select   * from message `;
        // const cartStr = 'SELECT cart_id,product.product_id,product_name,product_uprice,product_img_url,goods_num, product_num,shop_name FROM product,user,goods_cart,shop ' +
        //  'where goods_cart.user_id='${uId}' and product.product_id=goods_cart.product_id and user.user_id=goods_cart.user_id and shop.shop_id = product.shop_id ';

        db.query( keywordStr, (err, data) => {
            if (err) {
                var myObj = {
                    "status":"error",
                    "reason":"???"
                }
                res.json(res.myObj);
                console.log(err);
                //res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    var myObj = {
                        "status":"error",
                        "reason":"na datas"
                    }
                    res.json(res.myObj);
                    console.log(err);
                   // res.status(500).send('no datas').end();
                } else {
                    var backendData = data.result;
                    var resultList = [];
                    var curHost = "";
                    var oneHostDataList = new Array();
                    var item = new Object();
                    item.status = "success";
                    item.num = data.length;
                    for (var i in data){
                        var temp = new Object();
                        temp.lat = data[i].latitude;
                        temp.lon = data[i].longitude;
                        temp.alt = data[i].altitude;
                        temp.name = data[i].name;
                        temp.message = data[i].message;
                        resultList.push(temp);
                    }
                        item.annotation = resultList;
                    var myObj = {
                        "status":"success",
                        "num":3,           //返回的标注数量
                        "annotation": [
                            { "lat":40.55, "lon":30.66, "alt":27.55 , "name":"成都" ,"message":"天府之国"},
                            { "lat":40.75, "lon":-30.68, "alt":27.55 , "name":"上海" ,"message":"我想去的地方"},
                            { "lat":40.85, "lon":30.66, "alt":27.55 , "name":"北京" ,"message":"首都"}
                        ]
                    }

                    res.json(item)
                    //res.send(data);
                }}
        })
    });


    function getHomeDatas(getHomeStr, res) {
        db.query(getHomeStr, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });
    };
    route.get('/news', (req, res) => {
        getNewsDatas(getNewsNames, res);
    });
    function getNewsDatas(getNewsNames, res) {
        db.query(getNewsNames, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });
    };

    route.get('/category', (req, res) => {
        getCateNamesDatas(getCateNames, res);
    });

    function getCateNamesDatas(getCateNames, res) {
        db.query(getCateNames, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });
    };
    route.get('/categorygoods', (req, res) => {
        let mId = req.query.mId;
        const sql = `select * from product,category where product.category_id=category.category_id and category.category_id='${mId}'`;
        getCateGoods(sql, res);
    });

    function getCateGoods(sql, res) {
        db.query(sql, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });
    }
    route.get('/detail', (req, res) => {
        let produId = req.query.mId;
        const imagesStr = `select image_url from product_image where product_id='${produId}'`;
        const productStr = `select * from product where product_id='${produId}'`;
        let detailDatas = [];
        db.query(imagesStr, (err, imgDatas) => {
            if (err) {
                console.error(err);
                res.status(500).send('database err').end();
            } else {
                detailDatas.push(imgDatas);
                db.query(productStr, (err, data) => {
                    if (err) {
                        console.error(err);
                        res.status(500).send('database err').end();
                    } else {
                        detailDatas.push(data);
                        res.send(detailDatas);
                    }
                });
            }
        });

    });
  route.get('/cart', (req, res) => {
        let uId = req.query.uId;
      const keywordStr = `select deleted ,product.product_id as product_id,product_name,product_img_url,product_price,cart_id ,goods_num from product,goods_cart where goods_cart.product_id=product.product_id and goods_cart.user_id='${uId}'`;
      // const cartStr = 'SELECT cart_id,product.product_id,product_name,product_uprice,product_img_url,goods_num, product_num,shop_name FROM product,user,goods_cart,shop ' +
          //  'where goods_cart.user_id='${uId}' and product.product_id=goods_cart.product_id and user.user_id=goods_cart.user_id and shop.shop_id = product.shop_id ';

        db.query( keywordStr, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }}
            })
        });
    route.get('/orders', (req, res) => {

        const keywordStr = `select   * from product,orders,user where product.product_id=orders.product_id and user.user_id=orders.user_id;`;
        // const cartStr = 'SELECT cart_id,product.product_id,product_name,product_uprice,product_img_url,goods_num, product_num,shop_name FROM product,user,goods_cart,shop ' +
        //  'where goods_cart.user_id='${uId}' and product.product_id=goods_cart.product_id and user.user_id=goods_cart.user_id and shop.shop_id = product.shop_id ';

        db.query( keywordStr, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }}
        })
    });
    route.get('/deleteOrder', (req, res) => {

        let pId =req.query.pId;
        const getU = `delete from orders where order_id='${pId}'`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '删除成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/searchOrder', (req, res) => {
        let keyWord = req.query.kw;

        const keywordStr = `select  *  from orders,product where orders.product_id=product.product_id and product_name like '%${keyWord}%'`;
        db.query(keywordStr, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });


    });
    route.get('/searchMessage', (req, res) => {
        let keyWord = req.query.kw;

        const keywordStr = `select  *  from message where name like '%${keyWord}%'`;
        db.query(keywordStr, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });


    });

    route.get('/search', (req, res) => {
        let keyWord = req.query.kw;
        let hot = req.query.hot;
        let priceUp = req.query.priceUp;
        let priceDown = req.query.priceDown;
        const keywordStr = `select  *  from product,shop where product.shop_id=shop.shop_id and product.product_name like '%${keyWord}%'`;
        const hotStr = `select  *  from product,shop where product.shop_id=shop.shop_id and product.product_name like '%${keyWord}%' order by product_comment_num desc`;
        const priceUpStr = `select  *  from product,shop where product.shop_id=shop.shop_id and product.product_name like '%${keyWord}%' order by product_uprice asc`;
        const priceDownStr = `select  *  from product,shop where product.shop_id=shop.shop_id and product.product_name like '%${keyWord}%' order by product_uprice desc`;
        if (keyWord != '') {
            if (hot != '') {
                getSearchDatas(hotStr, res);
            } else if (priceUp != '') {
                getSearchDatas(priceUpStr, res);
            } else if (priceDown != '') {
                getSearchDatas(priceDownStr, res);
            } else {
                getSearchDatas(keywordStr, res);
            }
        }

    });
    /**
        get search datas
    */
    function getSearchDatas(keywordStr, res) {
        db.query(keywordStr, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data);
                }
            }
        });
    }
    /*
     *user reg func
     */
    route.post('/reg', (req, res) => {

        let mObj = {};
        for (let obj in req.body) {
            mObj = JSON.parse(obj);
        }
        let regName = mObj.regName;
        let regPasswd = mObj.regPasswd;
        regPasswd = common.md5(regPasswd + common.MD5_SUFFXIE);
        const insUserInfo = `INSERT INTO USERINFO(user_name,login_password,role) VALUES('${regName}','${regPasswd}',1)`;
        delReg(insUserInfo, res);
    });
    /*
     *deal user register
     */
    function delReg(insUserInfo, res) {
        db.query(insUserInfo, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '注册成功', 'status': 1 }).end();
            }
        })
    };
    route.post('/login', (req, res) => {

        let mObj = {};
        for (let obj in req.body) {
            mObj = JSON.parse(obj);
            console.log(mObj);
        }
        let username = mObj.loginName;
        let password = common.md5(mObj.loginPawd + common.MD5_SUFFXIE);;
        // console.log(username, mObj.passwd);
        const selectUser = `SELECT * FROM USERINFO where user_name='${username}'`;
        db.query(selectUser, (err, data) => {
            if (err) {
                console.log(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                if (data.length == 0) {
                    res.send({ 'msg': '该用户不存在', 'status': -1 }).end();
                } else {
                    let dataw = data[0];
                    //login sucess
                    if (dataw.login_password === password) {
                        //save the session
                        req.session['user_id'] = dataw.user_id;
                        req.session['role']=dataw.role;
                        dataw.msg = "登录成功";
                        dataw.status = 1;
                        res.send(dataw).end();
                    } else {
                        res.send({ 'msg': '密码不正确', 'status': -2 }).end();
                    }
                }
            }
        });

    });
    route.get('/userinfo', (req, res) => {
        let uId = req.query.uId;
        const getU = `SELECT role,user_name,user_number user_id FROM user where user_id='${uId}'`;
        db.query(getU, (err, data) => {
            if (err) {
                console.log(err);
                res.status(500).send('database err').end();
            } else {
                if (data.length == 0) {
                    res.status(500).send('no datas').end();
                } else {
                    res.send(data[0]);
                }
            }
        });
    });
    route.get('/addCart', (req, res) => {
        let uId = req.query.uId;
        let pId =req.query.pId;
        const getU = `insert into goods_cart (user_id,product_id,goods_num,created)values( '${uId}','${pId}',1,now())`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '添加成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/deleteCart', (req, res) => {
        let uId = req.query.uId;
        let pId =req.query.pId;
        const getU = `delete from goods_cart where user_id= '${uId}' and product_id='${pId}'`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '删除成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/deleteMessage', (req, res) => {

        let pId =req.query.pId;
        const getU = `delete from message where id='${pId}'`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '删除成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/deleteNews', (req, res) => {

        let pId =req.query.pId;
        const getU = `delete from news where id='${pId}'`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '删除成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/updateMessage', (req, res) => {
        let name=req.query.name;
        let message=req.query.message;
        let id=req.query.id;
        let long=req.query.long;
        let lat=req.query.lat;
        const getU = `update message set name='${name}' ,message='${message}'  ,latitude='${lat}'  ,longitude='${long}'  where id='${id}'`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '更新成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/updateNews', (req, res) => {
        let content=req.query.content;
        let num=req.query.num;
        let id=req.query.id;
        let date=req.query.date;
        const getU = `update news set content='${content}' ,num='${num}' ,dateday='${date}'  where id='${id}'`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {
                res.send({ 'msg': '更新成功', 'status': 1 }).end();
            }
        })
    });
    route.get('/createMessage', (req, res) => {
        let name=req.query.name;
        let message=req.query.message;
        let long=req.query.long;
        let lat=req.query.lat;
        let alt=req.query.alt;
        const getU = `insert into message (name,message,longitude,latitude,altitude) values ( '${name}' ,'${message}'  ,'${long}'  ,'${lat}' ,'${alt}')`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                var myObj = {
                    "status":"error",
                    "reason":"???"
                }
                res.json(res.myObj);
                //res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {

               res.send({ 'msg': '更新成功', 'status': 1 }).end();
            }
        })
    });

    route.get('/createMessageJson/name=:name/message=:message/long=:long/lat=:lat/alt=:alt', (req, res) => {
        let name=getQueryString("name");
        let message=getQueryString("message");
        let long=getQueryString("long");
        let lat=getQueryString("lat");
        let alt=getQueryString("alt");
        const getU = `insert into message (name,message,longitude,latitude,altitude) values ( '${name}' ,'${message}'  ,'${long}'  ,'${lat}' ,'${alt}')`;
        db.query(getU, (err) => {
            if (err) {
                console.error(err);
                var myObj = {
                    "status":"error",
                    "reason":"???"
                }
                res.json(res.myObj);
                //res.send({ 'msg': '服务器出错', 'status': 0 }).end();
            } else {

                res.send({ 'msg': '更新成功', 'status': 1 }).end();
            }
        })
    });



    return route;
}
function getQueryString(name) {
    var reg = new RegExp("(^|&)" + name + "=([^&]*)(&|$)", "i");
    var r = window.location.search.substr(1).match(reg);
    if (r != null) return decodeURI(r[2]); return null;
}
